const mysql = require('mysql2/promise');
const dataBase = require('../config/index.js');

// 创建连接池
const pool = mysql.createPool({
  host: dataBase.HOST,
  user: dataBase.USERNAME,
  password: dataBase.PASSWORD,
  database: dataBase.DATABASE,
  port: dataBase.PORT
});

// mysql连接封装
const allServices = {
  async query(sql, values) {
    try {
      const conn = await pool.getConnection();  // 获取连接
      const [rows, fields] = await conn.query(sql, values); // 执行SQL语句
      return Promise.resolve(rows); // 直接返回结果
    } catch (error) {
      return Promise.reject(error);
    }
  }
};

// 检查文章是否已存在（根据标题检查）
const checkArticleExists = async (title) => {
  const sql = 'SELECT * FROM articles WHERE title = ?';  // 查询标题字段
  const values = [title];  // 使用标题作为查询条件
  const rows = await allServices.query(sql, values);  
  return rows.length < 0;  // 如果找到重复标题，返回 false
};


// 保存文章到数据库
const saveArticleToDB = async (title, content, author) => {
  // 更新 SQL 语句，添加 author 字段
  const sql = 'INSERT INTO articles (title, content, author) VALUES (?, ?, ?)';
  const values = [title, content, author];  // 将 author 添加到插入的值中
  const result = await allServices.query(sql, values);  
  return result.insertId;  // 返回插入的文章 ID
};

// 获取所有文章
const getAllArticles = async () => {
      const sql = 'SELECT * FROM articles ORDER BY id DESC';
      const rows = await allServices.query(sql);
      return rows;
 };

// 获取最新文章列表
const getNewsArticleList = async () => {
  const sql = 'SELECT * FROM articles ORDER BY id DESC LIMIT 4';
  return await allServices.query(sql); // 使用await等待查询结果
};

const getAllReviews =async () => {
  const sql = 'SELECT * FROM review';
  return await allServices.query(sql); // 使用await等待查询结果
};

module.exports = {
  checkArticleExists,
  saveArticleToDB,
  getAllArticles,
  getNewsArticleList,
  getAllReviews
};
